﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.IO;

namespace Thanhlichtek.BEF.Win
{
    public class ExcelHelper
    {
        private Application app = null;
        private Workbook workbook = null;
        private Worksheet worksheet = null;
        private Range workSheet_range = null;
        private String _reportsFolder = "C:\\BullhornReports";
        public ExcelHelper()
        {
            createDoc();
        }
        public void createDoc()
        {
            try
            {


                app = new Application();
                app.Visible = false;
                workbook = app.Workbooks.Add(1);
                worksheet = (Worksheet)workbook.Sheets[1];

            }
            catch (Exception e)
            {
                Console.Write("Error");
            }
            finally
            {

            }
        }

        public void createHeaders(int row, int col, string htext, string cell1, string cell2, int mergeColumns, string b, bool font, int size, string fcolor)
        {
            worksheet.Cells[row, col] = htext;
            workSheet_range = worksheet.get_Range(cell1, cell2);
            workSheet_range.Merge(mergeColumns);
            switch (b)
            {
                case "YELLOW":
                    workSheet_range.Interior.Color = System.Drawing.Color.Yellow.ToArgb();
                    break;
                case "GRAY":
                    workSheet_range.Interior.Color = System.Drawing.Color.Gray.ToArgb();
                    break;
                case "GAINSBORO":
                    workSheet_range.Interior.Color = System.Drawing.Color.Gainsboro.ToArgb();
                    break;
                case "Turquoise":
                    workSheet_range.Interior.Color = System.Drawing.Color.Turquoise.ToArgb();
                    break;
                case "PeachPuff":
                    workSheet_range.Interior.Color = System.Drawing.Color.PeachPuff.ToArgb();
                    break;
                default:
                    //  workSheet_range.Interior.Color = System.Drawing.Color..ToArgb();
                    break;

            }

            workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
            workSheet_range.Font.Bold = font;
            workSheet_range.ColumnWidth = size;
            if (fcolor.Equals(""))
            {
                workSheet_range.Font.Color = System.Drawing.Color.White.ToArgb();
            }
            else
            {
                workSheet_range.Font.Color = System.Drawing.Color.Black.ToArgb();
            }

        }
        public void createHeaders(int row, int col, string htext)
        {
            worksheet.Cells[row, col] = htext;
            

        }
        public void addData(int row, int col, string data, string cell1, string cell2, string format)
        {
            worksheet.Cells[row, col] = data;
            workSheet_range = worksheet.get_Range(cell1, cell2);
            workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb();
            workSheet_range.NumberFormat = format;
        }

        public void addData(int row, int col, string data)
        {
            worksheet.Cells[row, col] = data;
        }

        public void addData(int row, int col, int data)
        {
            worksheet.Cells[row, col] = data.ToString();
        }
        
        public void addChart(int row)
        {
            ChartObjects charts = (ChartObjects)worksheet.ChartObjects(Type.Missing);
            ChartObject chartObject = (ChartObject)charts.Add(400, 10, 300, 250);
            Chart chart = chartObject.Chart;
            chart.HasTitle = true;
            chart.ChartTitle.Text = "";
            chart.ChartType = XlChartType.xlCylinderBarClustered;
            chart.RightAngleAxes = true;
            chart.AutoScaling = true;

            Range chartRange = worksheet.get_Range("A1", "E" + row.ToString());
            chart.SetSourceData(chartRange);
        }

        public void save(String fileName)
        {
            if (!Directory.Exists(_reportsFolder))
            {
                Directory.CreateDirectory(_reportsFolder);
            }
            var timeStamp = DateTime.Now.ToString("yyyyMMdd_hhmmss");
            worksheet.SaveAs(Path.Combine(_reportsFolder, String.Format("{0}_{1}", fileName, timeStamp)));
            app.Quit();
        }
    }
}
